import knexpkg from 'knex'
import dayjs from 'dayjs'
import { SchemaInspector } from 'knex-schema-inspector'

export const knex = knexpkg({
  client: 'mysql2',
  connection: {
    host: process.env.MYSQL_HOST,
    port: Number(process.env.MYSQL_PORT),
    user: process.env.MYSQL_USERNAME,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
    charset: 'utf8',
    // dateStrings: true,
    supportBigNumbers: true,
    bigNumberStrings: true,
    decimalNumbers: true,
  },
  pool: { min: 0, max: 20 },
})

export const knexType = [
  { typname: 'string', remark: '字符型' },
  { typname: 'tinyint', remark: '短整型_2' },
  { typname: 'smallint', remark: '短整型_4' },
  { typname: 'integer', remark: '整型_8', alias: 'int' },
  { typname: 'bigint', remark: '长整型_20' },
  { typname: 'double', remark: '双精度' },
  { typname: 'decimal', default: '10,2', remark: '数值型' },
  { typname: 'boolean', remark: '布尔型' },
  { typname: 'timestamp', default: '6', remark: '时间戳', alias: 'datetime' },
  { typname: 'text', remark: '文本型' },
  { typname: 'mediumtext', remark: '中文本型' },
  { typname: 'longtext', remark: '长文本型' },
  { typname: 'binary', remark: '二进制' },
  { typname: 'json', remark: 'json型' },
  { typname: 'jsonb', remark: 'json型' },
]

// 根据表名、行 id 软删除
export const deleteRow = async (table, id, userId) => {
  try {
    // 处理非规范id传参
    if (!['String', 'Number'].includes(type(id)) || !/^[1-9][0-9,]*$/.test(id.toString())) {
      return 'id 传参错误！'
    }
    const ids = (id.toString().includes(',')) ? id.split(',') : [id]
    const sql = `update ${table} set is_deleted=id, deleted_by=${userId}, deleted_at='${dayjs().format('YYYY-MM-DD HH:mm:ss.SSS')}' where id in (${ids})`
    await knex.raw(sql)
    return null
  } catch (error) {
    return error.toString()
  }
}
// 根据表名、行 id 硬删除
export const deleteRowHard = async (table, id) => {
  const trx = await knex.transaction()
  try {
    // 处理非规范id传参
    if (!['String', 'Number'].includes(type(id)) || !/^[1-9][0-9,]*$/.test(id.toString())) {
      return 'id 传参错误！'
    }
    const ids = (id.toString().includes(',')) ? id.split(',') : [id]
    await trx(table).whereIn('id', ids).del()
    await trx.commit()
    return null
  } catch (error) {
    await trx.rollback()
    return error
  } finally {
    await trx.destroy() // 销毁事务，释放连接
  }
}

// 获取字段
export const getColumns = async (table, schema = '') => {
  const inspector = SchemaInspector(knex)
  if (isNotNilOrEmpty(schema) && inspector.withSchema) {
    try {
      inspector.withSchema(schema)
      const columns = await inspector.columnInfo(table)
      return columns
    } catch (e) {
      console.log(e)
      return null
    }
  }
  const columns = await inspector.columnInfo(table)
  return columns
}

// 数据库字段类型：数值型
// const IntegerType = 'INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT'
// const FixedPointTypes = 'DECIMAL,NUMERIC'
// const FloatingPointTypes = 'FLOAT,DOUBLE'
export const NUMERIC_DATA_TYPES = ['integer', 'int', 'smallint', 'tinyint', 'boolean', 'mediumint', 'bigint', 'decimal', 'numeric', 'float', 'double']
// 数据库字段类型：日期型
export const DATE_TIME_DATA_TYPES = ['date', 'time', 'datetime', 'timestamp', 'year']
// 数据库字段类型：字符型
export const STRING_DATA_TYPES = ['char', 'varchar', 'binary', 'varbinary', 'blob', 'text', 'enum', 'set']
// 系统保留字段
export const RESERVED_FIELD = ['created_at', 'created_by', 'updated_at', 'updated_by', 'deleted_at', 'deleted_by', 'is_deleted', 'version']
// 数据库必需字段
export const STANDARD_FIELD = [
  { columnName: 'id', columnType: 'bigint' },
  { columnName: 'created_at', columnType: 'timestamp', default: '1', comment: '创建时间' },
  { columnName: 'created_by', columnType: 'bigint', comment: '创建人' },
  { columnName: 'updated_at', columnType: 'timestamp', comment: '更新时间' },
  { columnName: 'updated_by', columnType: 'bigint', comment: '更新人' },
  { columnName: 'deleted_at', columnType: 'timestamp', comment: '删除时间' },
  { columnName: 'deleted_by', columnType: 'bigint', comment: '删除人' },
  { columnName: 'is_deleted', columnType: 'tinyint', default: 0, comment: '是否删除 默认 0, 删除复制id值，便于索引' },
  // { columnName: 'version', columnType: 'integer', default: 0, comment: '乐观锁' },
  { columnName: 'remark', columnType: 'string', comment: '备注' },
]

// 新增或更新表数据
export const upsertRows = async (reqTable, datas, headerUserId, hideIDs, numFromStr = true) => {
  let table = reqTable
  let schema
  if (reqTable.split('.').length > 1) {
    schema = reqTable.split('.')[0]
    table = reqTable.split('.')[1]
  }
  // 获取数据表字段
  const columns = await getColumns(table, schema)
  if (isNilOrEmpty(columns)) {
    return { code: 301, msg: '必填参数 datas 为空！' }
  }
  const columnFields = columns.filter(e => !RESERVED_FIELD.includes(e.name)).map(e => e.name)
  if (!columnFields.includes('id')) {
    return { code: 301, msg: '数据表必须包含 id 字段！' }
  }
  const upsertIds = []
  // 检测不正确的 Number 字段格式
  let notValid
  const formData = datas.map((e) => {
    const item = clone(e)
    Object.keys(item).forEach(v => {
      if (item[v] === null) {
        delete item[v]
      }
    })
    Object.keys(item).some(v => {
      // 删除非数据表字段
      if (!columnFields.includes(v)) {
        delete item[v]
      } else {
        const columnItem = columns.find(n => n.name === v)
        if (item[v] === 'NULL') {
          // 设置为空
          item[v] = null
        } else if (NUMERIC_DATA_TYPES.includes(columnItem.data_type) && type(item[v]) !== 'Number') {
          // 字段类型为 Number ，但传参为其他类型，提示错误
          if (numFromStr === true) {
            if (isNaN(Number(item[v])) || isNilOrEmpty(item[v])) {
            // 转换不了或者为空值，则删掉不处理。
              delete item[v]
            } else {
              // 能转换成数值的，转换成数值
              item[v] = BigInt(item[v])
            }
            return false
          }
          notValid = `字段 ${v} 的值类型错误！传参类型为 ${type(item[v])}，应为 Number 。`
          return true
        }
        // json 格式转换
        if (columnItem.data_type === 'json' && type(item[v]) !== 'String') {
          item[v] = JSON.stringify(item[v])
        }
      }
      return false
    })
    return item
  })
  if (isNilOrEmpty(formData)) {
    return { code: 301, msg: '数据为空或数据表字段名错误！' }
  }
  if (isNotNilOrEmpty(notValid)) {
    return { code: 301, msg: notValid }
  }
  const trx = await knex.transaction()
  try {
    for (let i = 0; i < formData.length; i++) {
      const item = formData[i]
      if (isNotNilOrEmpty(item.id)) {
        if (headerUserId) {
          item.updated_by = headerUserId
        }
        item.updated_at = dayjs().format('YYYY-MM-DD HH:mm:ss.SSS')
        if (isNilOrEmpty(schema)) {
          await trx(table).update(item).where({ id: item.id })
        } else {
          await trx(table).withSchema(schema).update(item).where({ id: item.id })
        }
        upsertIds.push(item.id.toString())
      } else {
        if (headerUserId) {
          item.created_by = headerUserId
        }
        let returnId
        if (isNilOrEmpty(schema)) {
          returnId = await trx(table).insert(item, ['id'])
        } else {
          returnId = await trx(table).withSchema(schema).insert(item, ['id'])
        }
        upsertIds.push(returnId[0].toString())
      }
    }
    // TODO: 记录操作日志
    await trx.commit()
  } catch (error) {
    await trx.rollback()
    return { code: 500, msg: '更新/插入失败。', result: error }
  } finally {
    await trx.destroy() // 销毁事务，释放连接
  }
  if (isNilOrEmpty(upsertIds)) {
    return { code: 301, msg: '更新/插入失败，未进行任何操作。' }
  }
  const result = hideIDs ? upsertIds.length : upsertIds
  return { code: 0, msg: 'success', result }
}

export const getUpsertData = (payload) => {
  const { reqData, colConfig, userId } = payload
  const formData = isArray(reqData) ? clone(reqData) : clone([reqData])
  const header_userid = colConfig[0].table_cols_header_userid || []
  const tableCols = colConfig[0].table_cols_selected || []
  formData.forEach(e => {
    Object.keys(e).forEach(v => {
      if (!tableCols.includes(v)) {
        delete e[v]
      }
      header_userid.forEach(v => {
        if (tableCols.includes(v)) {
          e[v] = userId
        }
      })
    })
  })
  return formData
}
